<?php



class CustomerAction extends Action {
	
	
	//分享增加积分
	function shareSuccess(){
		
		$openid = $_GET['openid'];
		$point = 1;
		
		$sql = "insert into AddIntegral
        (id,CustomerId,DeductDate,DeductIntegral,Remarks, fstate,IssueDate,Shop,flag,SrcFrom)                    
 values(SUBSTRING(REPLACE(NEWID(),'-', ''),1, 10), '',
          getdate(), '{$point}', '分享赠送积分',
          '0', GETDATE(), (select top 1 shop from bd_customers
 where wxOpenID = '{$openid}'), '0', 'wx')";
		$ob = M();
		$info = $ob->query($sql);
		
		$sql = "update  bd_Customers 
				set Integral = ISNULL(Integral, 0.00) + '{$point}' 
					where wxOpenID = '{$openid}';";
		$ob1 = M();
		$info = $ob1->query($sql);
		
		echo Encode(array('info'=>$info));
	}
	
	function bind(){
		$openid = $_GET['openid'];
		$mobile = $_GET['mobile'];
		if(empty($mobile) || empty($openid)){
			echo Encode('failure');
			return ;
		}
		$save['wxOpenID'] = $openid;
		$ob = D('Customer');
		$info = $ob->where(array('mobile'=>$mobile))->save($save);
		echo Encode('success');
	}
	
    public function InfoByOpenID(){
    	$openid = $_GET['openid'];
    	$ob = D('Customer');
    	$info = $ob->where(array('wxOpenID'=>$openid))->field('"Id",
  "CustomerKindNo",
  "CustomerNo",
  "Email",
  "Facts",
  "Fax",
  "FullName",
  "Name",
  "Account",
  "Address",
  "Bank",
  "Country",
  "Province",
  "City",
  "Contact",
  "Corporation",
  "Area",
  "PostCode",
  "TaxPayingAccount",
  "Tel",
  "Http",
  "DeliverDestination",
  "Remarks",
  "Fstate",
  "BirthDate",
  "Sex",
  "Mobile",
  "IssueDate"')->find();
    	
    	
    	if(empty($info)){
    		$info['NoUser'] = '1';
    		echo Encode($info);
    		return ;
    	}
    	
//     	$sql = "select convert(numeric(12,2), SUM(Balance)) as yue from bd_CardDetail a
// left join bd_CardClass b on a.CardClassId = b.Id
// left join bd_Customers c on c.id = a.customerid
// where SUBSTRING(b.CardKindNo, 1, 3) = '001' and a.fstate = '0'
// and (a.EffectiveDate >= GETDATE() or EffectiveDate is null)
// and a.CustomerId = '{$info[Id]}'";

    	$sql = "
    	select convert(numeric(12,2), SUM(Balance)) as Balance from bd_CardDetail a
left join bd_CardClass b on a.CardClassId = b.Id
left join bd_Customers c on c.id = a.customerid
where SUBSTRING(b.CardKindNo, 1, 3) = '001' and a.fstate = '0' 
and (a.EffectiveDate >= GETDATE() or EffectiveDate is null)
and (a.CustomerId = '{$info[Id]}' OR c.Mobile = '{$info[Mobile]}')		
    	";
    	
    	$yue = $ob->query($sql);
    	
    	$info['yue'] = $yue[0]['yue'];
    	echo Encode($info);
    }
    
    
    public function InfoByMobile(){
    	$mobile = $_GET['mobile'];
    	$ob = D('Customer');
    	$info = $ob->where(array('mobile'=>$mobile))
    				->field('"Id",
  "CustomerKindNo",
  "CustomerNo",
  "Email",
  "Facts",
  "Fax",
  "FullName",
  "Name",
  "Account",
  "Address",
  "Bank",
  "Country",
  "Province",
  "City",
  "Contact",
  "Corporation",
  "Area",
  "PostCode",
  "TaxPayingAccount",
  "Tel",
  "Http",
  "DeliverDestination",
  "Remarks",
  "Fstate",
  "BirthDate",
  "Sex",
  "Mobile",
  "IssueDate"')->find();
    	echo Encode($info);
    }
    
    //取储值卡
    function Card(){
    	
    	//0000113158
    	$id = $_GET['id'];
    	if(empty($id)){
    		echo Encode(array());
    		return ;
    	}
//     	$id = '0000113158';
    	$sql = "Select CardNo as ValueCardNo, CardType as ValueCardType,
	convert(numeric(15,2),Balance) as ValueCardBalance,
	NonePay,bd_CardDetail.Remarks as Remark
    from bd_CardDetail,bd_Customers
    where isnull(bd_CardDetail.fstate,0)<>'-1'
    and bd_CardDetail.EffectiveDate>= getdate()
    and bd_CardDetail.CustomerId=bd_Customers.id  and  bd_CardDetail.pflag='0'
    and ( bd_CardDetail.id='{$id}' or bd_CardDetail.CustomerId='{$id}' or bd_Customers.mobile = '{$id}')";
    	
    	$ob = M();
    	$info = $ob->query($sql);
    	
    	echo Encode($info);
    }
    
    //疗程项目
    function lc(){
    	
    	$id = $_GET['id'];
    	if(empty($id)){
    		echo Encode(array());
    		return ;
    	}
//     	$id = '0000113158';
    	$sql = "
    			
         Select CardNo,CardType,
          (select ProjectName from bd_ProjectSet where bd_ProjectSet.id=bd_ProjectAndTimes.ProjectId) as name,
          convert(numeric(15,3),Quantity-isnull(UseQuantity,0)) as LeftCount,
          convert(numeric(15,1),bd_ProjectAndTimes.UseCardPrice) as UseCardPrice,
         (convert(numeric(15,3),Quantity-isnull(UseQuantity,0)))*convert(numeric(15,1),bd_ProjectAndTimes.UseCardPrice) as FMoney,
         bd_CardDetail.NonePay as NonePay, 
         case when isnull(bd_CardDetail.Remarks, '') <> '' and isnull(bd_ProjectAndTimes.Remark, '') <> ''  then
         (bd_CardDetail.Remarks + ','+ bd_ProjectAndTimes.Remark) 
          when isnull(bd_CardDetail.Remarks, '') <> '' then bd_CardDetail.Remarks 
          when isnull(bd_ProjectAndTimes.Remark, '') <> '' then bd_ProjectAndTimes.Remark else '' end as Remark,
         case when  isnull(bd_CardDetail.initfstate,'0')<>'1' then
         convert(numeric(12,2),(isnull(CardTakeOutRate, 0))*convert(numeric(15,2),bd_ProjectAndTimes.UseCardPrice) * 0.01)
         else isnull(SaleAchievement, '0') end as SingleSaleAchievement
         , bd_ProjectAndTimes.projectid,bd_ProjectAndTimes.CardDetailId, bd_ProjectAndTimes.id as bd_ProjectAndTimesID
         from bd_CardDetail,bd_ProjectAndTimes,bd_Customers,bd_ProjectSet
         where  isnull(bd_CardDetail.fstate,0)<>'-1' and bd_CardDetail.EffectiveDate>= getdate()
         and  bd_CardDetail.CustomerId=bd_Customers.id and  bd_CardDetail.id=bd_ProjectAndTimes.CardDetailId 
         and convert(numeric(15,3),Quantity-isnull(UseQuantity,0))>0 and bd_CardDetail.pflag<>'0'
         and bd_ProjectAndTimes.ProjectId=bd_ProjectSet.id 
         and ( bd_CardDetail.CustomerId='$id' or bd_Customers.mobile = '$id')
         
         ";
    	
    	
    	$ob = M();
    	$info = $ob->query($sql);
    	
    	foreach($info as $key=>$value){
    		$info[$key]['LeftCount'] = str_replace('.000','',$info[$key]['LeftCount']);
    		$info[$key]['Money'] = str_replace(array('.0000','.000','.00','.0'),'',$info[$key]['Money']);
    	}
    	 
    	echo Encode($info);
    }
    
    //赠送项目
    function gift(){
    	
    	$id = $_GET['id'];
//     	$id = '0000113158';
    	
//     	$sql = " Select '项目赠送' as SendValueCardType,(select SendType from bd_SendTypeSet where bd_SendTypeSet.id=SS_SendProject.SendTypeId) as SendType,SendDate, 
//         (select ProjectName from bd_ProjectSet where SS_SendProject.ProjectId=bd_ProjectSet.id ) as SendDetail,
//         Times-isnull(ConsumeQuantity,0) as LastQuantity,Remarks as SendMark from SS_SendProject 
//         where SS_SendProject.EffectiveDate>=getdate()
//         and Times-isnull(ConsumeQuantity,0)>0 and SS_SendProject.CustomerId= '会员ID'
//         and SS_SendProject.Fstate<>'-1'
//         union all
//         select '产品赠送' as 类型,(select SendType from bd_SendTypeSet where bd_SendTypeSet.id=SS_SendGoods.SendTypeId) as 赠送类型,SendDate as 赠送日期,
//         (select GoodsName from bd_Goods where SS_SendGoods.GoodsId=bd_Goods.id) as 品项明细,
//         Convert(numeric(15,2), Quantity-isnull(ConsumeQuantity,0)) as 剩余数量,Remarks as 备注 from SS_SendGoods where 
//         Quantity-isnull(ConsumeQuantity,0)>0 and SS_SendGoods.CustomerId='会员ID'
//         and SS_SendGoods.Fstate<>'-1'
//         union all
//         select '产品寄存' as 类型,'',SendDate as 寄存日期,
//         (select GoodsName from bd_Goods where SS_StoreGoods.GoodsId=bd_Goods.id) as 品项明细,
//         Quantity-isnull(ConsumeQuantity,0) as 剩余数量,Remarks as 备注 from SS_StoreGoods where
//         Quantity-isnull(ConsumeQuantity,0)>0 
//         and SS_StoreGoods.Fstate<>'-1'
//         and SS_StoreGoods.CustomerId='会员ID'  ";

    	$sql = "
	    Select '项目赠送' as SendValueCardType,(select SendType from bd_SendTypeSet where bd_SendTypeSet.id=SS_SendProject.SendTypeId) as SendType,SendDate, 
        (select ProjectName from bd_ProjectSet where SS_SendProject.ProjectId=bd_ProjectSet.id ) as SendDetail,
        Times-isnull(ConsumeQuantity,0) as LastQuantity,Remarks as SendMark from SS_SendProject 
        where SS_SendProject.EffectiveDate>=getdate()
        and Times-isnull(ConsumeQuantity,0)>0 
        and (SS_SendProject.CustomerId= '会员ID' or 
          exists(select id from bd_Customers aa where SS_SendProject.CustomerId = aa.ID and aa.Mobile = '手机号'))
        and SS_SendProject.Fstate<>'-1'
        union all
        select '产品赠送' as SendValueCardType,(select SendType from bd_SendTypeSet where bd_SendTypeSet.id=SS_SendGoods.SendTypeId) as SendType,SendDate,
        (select GoodsName from bd_Goods where SS_SendGoods.GoodsId=bd_Goods.id) as SendDetail,
        Convert(numeric(15,2), Quantity-isnull(ConsumeQuantity,0)) as LastQuantity,Remarks as SendMark from SS_SendGoods where 
        Quantity-isnull(ConsumeQuantity,0)>0 
        and (SS_SendGoods.CustomerId='会员ID' or
        exists(select id from bd_Customers aa where SS_SendGoods.CustomerId = aa.ID and aa.Mobile = '手机号'))
        and SS_SendGoods.Fstate<>'-1'
        union all
        select '产品寄存' as SendValueCardType,'',SendDate,
        (select GoodsName from bd_Goods where SS_StoreGoods.GoodsId=bd_Goods.id) as SendDetail,
        Quantity-isnull(ConsumeQuantity,0) as LastQuantity,Remarks as SendMark from SS_StoreGoods where
        Quantity-isnull(ConsumeQuantity,0)>0 
        and SS_StoreGoods.Fstate<>'-1'
        and (SS_StoreGoods.CustomerId='会员ID' or
        exists(select id from bd_Customers aa where SS_StoreGoods.CustomerId = aa.ID and aa.Mobile = '手机号'))			
    	

    			";
    	
    	$ob = M();
    	$info = $ob->query(str_replace(
    			array('会员ID','手机号'),
    			 array($id,$id), 
    			$sql));
    	echo Encode($info);
    }
    
    //欠款
    function nonePay(){
    	$sql = "
			select '购卡' as DebtType,bd_CardDetail.RegisterDate,CardNo as DebtCardNo,CardType as DebtCardType,convert(numeric(15, 2), NonePay) as NonePay from bd_CardDetail 
           where isnull(NonePay,0)>0  and ( bd_CardDetail.id='会员ID' or bd_CardDetail.CustomerId='会员ID' or
            exists(select id from bd_Customers aa where bd_CardDetail.CustomerId = aa.ID and aa.Mobile = '手机号'))
           union all 
           select '充值' as 类型,SS_CustomerAddMoney.PayDate,CardNo,CardType, convert(numeric(15, 2), SS_CustomerAddMoney.NonePay) as NonePay from SS_CustomerAddMoney,bd_CardDetail 
           where isnull(SS_CustomerAddMoney.flag,0)<>'-1' and Isnull(SS_CustomerAddMoney.AddMoney,0)<>0 and isnull(SS_CustomerAddMoney.NonePay,0)>0 
           and SS_CustomerAddMoney.CardDetailId=bd_CardDetail.Id  
           and ( bd_CardDetail.id='会员ID' or bd_CardDetail.CustomerId='会员ID' or
           exists(select id from bd_Customers aa where bd_CardDetail.CustomerId = aa.ID and aa.Mobile = '手机号'))
           union all 
           select '消费' as 类型,b.SaleDate,(select CardNo from bd_CardDetail where  b.CardDetailId=bd_CardDetail.Id) as CardNo,ProjectName, convert(numeric(15, 2), b.NonePay) as NonePay
           from ( 
           select CardDetailId, a.SaleDate,ProjectName,NonePay as NonePay from ( 
           select CardDetailId,a.SaleDate,isnull(QianKuan,0)-isnull(b.HandBalance,0) as NonePay,
           (select ProjectName from bd_ProjectSet where bd_ProjectSet.ID=b.ProjectId) as ProjectName
           from SS_Consume a
           left join SS_ConsumeProjectDetail b on a.mdid=b.mdid 
           left join bd_CardDetail c on b.CardDetailId=c.id 
           where                    
           a.fstate='1'  and isnull(Pstate,0)<>'1' 
           and isnull(QianKuan,0)-isnull(b.HandBalance,0)<>0  
           and (c.id='会员ID' or c.CustomerId='会员ID' or b.FuWuCustomerId='会员ID' or
           exists(select id from bd_Customers aa where b.FuWuCustomerId = aa.ID and aa.Mobile = '手机号'))           
           ) a -- group by CardDetailId,品项,SaleDate
           union all 
           select CardDetailId,a.SaleDate, (select GoodsName from bd_goods where bd_goods.Id=b.GoodsId) as ProjectName,
           convert(numeric(15, 2), isnull(QianKuan,0)-isnull(b.HandBalance,0)) as NonePay            
           from SS_Consume a
           left join SS_ConsumeGoodsDetail b on a.mdid=b.mdid 
           left join bd_CardDetail c on b.CardDetailId=c.id
           where           
           a.fstate='1' and isnull(Pstate,0)<>'1' 
           and isnull(QianKuan,0)-isnull(b.HandBalance,0)<>0  
           and ( c.id='会员ID' or c.CustomerId='会员ID' or b.FuWuCustomerId='会员ID' or
           exists(select id from bd_Customers aa where b.FuWuCustomerId = aa.ID and aa.Mobile = '手机号')) 
           ) as b
           where  convert(numeric(15, 2), ISNULL(b.NonePay, 0.00))<>0
    			";
    	
    	$id = $_GET['id'];
    	$ob = M();
    	$info = $ob->query(str_replace(
    			array('会员ID','手机号'), 
    			array($id,$id), 
    			$sql));
    	echo Encode($info);
    }
    
    
    //消费明细
    function consume(){
    	
    	$id = $_GET['id'];
    	if(empty($id)){
    		echo Encode(array());
    		return ;
    	}
    	$starttime = $_GET['starttime'];
    	$endtime = $_GET['endtime'];
    	
    	if(empty($starttime)){
    		$starttime = date('Y-m-d H:i:s',time()-86400*60);
    	}
    	
    	if(empty($endtime)){
    		$endtime = date('Y-m-d H:i:s',time()+86400);
    	}
    	
    	
    	$sql = "select * from 
(  
  select '前台消费' as type, aa.* from( 
     Select c.MDID, b.CardNo, b.CardType,convert(numeric(12,2), a.price * a.quantity * a.discount) as ConsumeMoney ,
     (isnull(a.Cash,0.00) + isnull(a.bank,0.00) + isnull(a.usecard,0.00)) as PayMoney,     
     (isnull(QianKuan, 0.00) - isnull(a.HandBalance, 0.00)) as NonePay, c.SaleDate, 
      a.fuwucustomerid as CustomerId, d.projectname as Name, a.Employee,a.ProjectGroupChieve as  GroupChieve
     from ss_consumeprojectdetail a -- 项目消费
     left join bd_CardDetail b on a.CardDetailId = b.Id
     left join ss_consume c on c.mdid = a.mdid 
     left join bd_projectset d on d.id = a.projectid 
     left join bd_customers e on e.id = a.fuwuCustomerId
     where 1=1 and (a.FuWuCustomerId = '会员ID' or e.Mobile = '手机号' )
     and SaleDate >= '填写时间开始条件' and SaleDate <= '填写时间结束条件'
     Union all
     Select c.MDID, b.CardNo, b.CardType, convert(numeric(12,2), a.price * a.quantity * a.discount) as ConsumeMoney,
     (isnull(a.Cash,0.00) + isnull(a.bank,0.00) + isnull(a.usecard,0.00)) as PayMoney,
     (isnull(QianKuan, 0.00) - isnull(a.HandBalance, 0.00)) as NonePay, c.SaleDate,
     a.fuwucustomerid as CustomerId, d.GoodsName, a.Employee,a.GoodsGroupChieve    
     from ss_consumeGoodsdetail a  -- 产品消费
     left join bd_CardDetail b on a.CardDetailId = b.Id
     left join ss_consume c on c.mdid = a.mdid
     left join bd_Goods d on d.id = a.GoodsId
     left join bd_customers e on e.id = a.fuwuCustomerId
     where 1=1 and (a.FuWuCustomerId = '会员ID' or e.Mobile = '手机号' )
     and SaleDate >= '填写时间开始条件' and SaleDate <= '填写时间结束条件'
   ) aa
 union all
 Select '购卡' as Type,a.Id, a.CardNo, a.CardType, a.SellMoney, 
       (ISNULL(a.UseCard, 0.00) + ISNULL(a.CardChange, 0.00) + isnull(a.Cash, 0.00) + isnull(a.Bank, 0.00)) as PayMoney,
       a.NonePay, a.RegisterDate, a.CustomerId, a.CardType, a.SaleMan, a.GroupChieve
       from bd_CardDetail a 
       left join bd_CardClass b on a.CardClassId = b.Id 
       left join bd_CardClass c on c.Id = b.Pid 
       left join bd_customers e on e.id = a.CustomerId       
       where isnull(a.Initfstate, '0') <> '1' and (CustomerId = '会员ID'  or e.Mobile = '手机号')
       and a.RegisterDate >= '填写时间开始条件' and a.RegisterDate <= '填写时间结束条件'
 Union all
 Select '充值' as type,a.MDID, b.CardNo, b.CardType, a.AddMoney, 
       isnull(a.Cash, 0.00)+ isnull(a.BankCard, 0.00) + isnull(a.UseCard, 0.00) as PayMoney,
       a.NonePay, a.PayDate, a.CustomerId, b.CardType, a.SaleMan, b.GroupChieve
       from SS_CustomerAddMoney a
       left join bd_CardDetail b on a.CardDetailId = b.Id
       left join bd_customers e on e.id = a.CustomerId              
       where (a.CustomerId = '会员ID' or e.Mobile = '手机号')
       and a.PayDate >= '填写时间开始条件' and a.PayDate <= '填写时间结束条件'
 Union all 
 Select a.BiaoShi as type, a.ID, b.CardNo, b.CardType,
       (isnull(a.Cash,0.00) + isnull(a.bankcard,0.00) + isnull(a.usecard,0.00)) as HandMoney,
       (isnull(a.Cash,0.00) + isnull(a.bankcard,0.00) + isnull(a.usecard,0.00)) as PayMoney,
       0.00 as NonePay, a.HandDate, b.CustomerId, b.CardType, a.Employees, b.GroupChieve
       From handbalance a 
       left join bd_CardDetail b on a.CardDetailId = b.Id
       left join bd_customers e on e.id = a.CustomerId                     
       where   
       (b.CustomerId = '会员ID' or e.Mobile = '手机号')
       and a.HandDate >= '填写时间开始条件' and a.HandDate <= '填写时间结束条件'
) aa 
order by SaleDate desc, type, MDID";
    	
    	$ob = M();
    	$info = $ob->query(str_replace(
    			array('会员ID','手机号','填写时间开始条件','填写时间结束条件'), 
    			array($id,$id,$starttime,$endtime), 
    			$sql));
    	
    	foreach($info as $key=>$value){
    		$date = (array)$value['SaleDate'] ;
    		$return[substr($date[date], 0,10)][] = $value;
    	}
    	
//     	print_r($return);
    	echo Encode($return);
    }
    
    
    function comment(){
    	
    	$ob = M()->table("dbo.wx_ConsumeEvaluation");
    	if($_GET['id']){
    		$info = $ob->where(array('SrcID'=>$_GET['id']))->find();
    		echo Encode($info);
    		return ;
    	}
    	
    	$add = $_POST;
    	$info1 = $ob->where(array('SrcID'=>$_POST['SrcID']))->find();
    	if(empty($info1)){
    		$obs = M()->table("dbo.wx_ConsumeEvaluation");
    		$obs->add($add);
    	}
    	
    }
    
    function Project(){
    	
    	$sql = " select a.ProjectId , c.ProjectName as Name, a.Quantity - a.UseQuantity as LeftCount, a.Usecardprice as Price
 from bd_ProjectAndTimes a
 left join bd_CardDetail b on a.CardDetailId = b.Id
 left join bd_ProjectSet c on c.ID = a.ProjectId
 where b.CustomerId = '会员ID'
 and (a.Quantity - a.UseQuantity) > 0";
    	
    	$id = $_GET['id'];
    	$ob = M();
    	$info = $ob->query(str_replace(
    			array('会员ID'),
    			array($id),
    			$sql));
    	
    	echo Encode($info);
    }
    
    function reserve(){
    	$add = $_POST;
    	$ob = M()->table('dbo.SS_OrderApply');
    	$ob->add($add);
    }
    
   
    function register(){
    	$name 	= $_POST['name'];
    	$mobile = $_POST['mobile'];
    	$sex 	= $_POST['sex'];
    	
    	$sql = "insert into bd_customers(
  fstate, Id, CustomerKindNo, 
  CustomerNo, Name, Mobile, 
  Sex, Shop,IDCard, 
  Tel,IssueDate, PassWord, 
  SrcFrom, CustomerType, Remarks)
values('0', SUBSTRING(REPLACE(NEWID(),'-', ''),1, 20), '',
       SUBSTRING(REPLACE(NEWID(),'-', ''),1, 5), '{$name}', '{$mobile}',
       '{$sex}', '', '', 
       '', GETDATE(), '1', 
       'wx', '新客', '微信关注')";
    	
    	$ob = M();
    	$info = $ob->query($sql);
    	
    	$openid = $_POST['openid'];
    	$save['wxOpenID'] = $openid;
    	$ob = D('Customer');
    	$info = $ob->where(array('mobile'=>$mobile))->save($save);
    	
    	echo Encode(array('info'=>$info));
    }
    
    //预约列表
    function BookList(){
    	
    	$openid = $_GET['openid'];
    	if($_GET['debug']=='1'){
    		$openid = "oyEL0t1BljoYFf2Sbw3ghqVi36RI";
    	}
    	$starTime = date('Y-m-d',time()-86400*180);
    	$endTime = date('Y-m-d',time()+86400*3);
    	
    	$sql = " Select b.id as CustomerID, b.Name as CustomerName, a.wxOpenID,
 case when a.fstate = 0 then 
    case when isnull(a.IsLook, '0') = '0' then '未授理' else '已授理' end 
 else '作废' end as TableSate, 
 c.Shop as ShopName, e.Name as EmployeeName, 
 case when isnull(a.IsLook, '0') <> '0' then d.PlanWakeUpDate else a.orderdate end as PlanBeginDate,
 case when isnull(a.IsLook, '0') <> '0' then d.planwakeupdateend else '' end as PlanEndDate
 from SS_OrderApply a 
 inner join bd_Customers b on (a.wxOpenID = b.wxOpenID or a.wxOpenID = b.mobile) 
 left join bd_ShopSet c on c.id = a.ShopId 
 left join SS_EmployeePlanDateing d on d.srcTableID = a.id and srcType = 'wxOrderApply'
 left join bd_employees e on e.id = d.EmployeeId
 where 1=1 and a.wxOpenID = '{$openid}' 
    		and a.orderdate >= '{$starTime}' and a.orderdate <= '{$endTime}'";
    	
    	$ob = M();
    	$info = $ob->query(str_replace(
    			array('会员ID'),
    			array(''),
    			$sql));
    	 
    	echo Encode($info);
    	
    }
    
    //消费评价
    function consumeprojectdetail(){
    	
    	$id = $_GET['srcID'];
    	$SrcType = $_GET['SrcType'];
    	$ob = M();
    	
    	//前台消费明细
    	$sql1 = "select m.*, n.Balance from 
(
 select aa.SaleDate, aa.Shop, aa.CardDetailId, aa.CardNo, aa.CardType, 
 SUM(usecard) as UseCard
 from (
 select b.SaleDate, d.Shop, isnull(c.UseCard, 0.00) as usecard, c.CardDetailId, e.CardNo, e.CardType
 from wx_SendConsumeRecord a 
 left join SS_Consume b on a.srcid = b.MDID
 left join SS_ConsumeProjectDetail c on c.MDID = b.MDID
 left join bd_ShopSet d on d.id = b.ShopId
 left join bd_CardDetail e on e.Id = c.CardDetailId
 left join bd_CardClass f on f.Id = e.CardClassId  
 where a.SrcID = '{$id}' and SUBSTRING(CardKindNo, 1, 3) <> '002'
 union all
 select b.SaleDate, d.Shop, isnull(c.UseCard, 0.00) as usecard, c.CardDetailId, e.CardNo, e.CardType
 from wx_SendConsumeRecord a 
 left join SS_Consume b on a.srcid = b.MDID
 left join SS_ConsumeGoodsDetail c on c.MDID = b.MDID
 left join bd_ShopSet d on d.id = b.ShopId
 left join bd_CardDetail e on e.Id = c.CardDetailId
 left join bd_CardClass f on f.Id = e.CardClassId  
 where a.SrcID = '{$id}' and SUBSTRING(CardKindNo, 1, 3) <> '002'
 ) aa group by aa.SaleDate, aa.Shop, aa.CardDetailId, aa.CardNo, aa.CardType
) m
left join bd_CardDetail n on m.CardDetailId = n.Id";
    	$info1 = $ob->query($sql1);
    	$return['qt'] = $info1;
    	
    	//购卡
    	$sql2 = " select b.RegisterDate, d.Shop, b.Remarks,
 ISNULL(Cash, 0.00) as Cash,
 ISNULL(b.Bank, 0.00) as Bank, 
 ISNULL(UseCard, 0.00) + ISNULL(CardChange, 0.00) as UseCard,
 b.NonePay
 from wx_SendConsumeRecord a 
 left join bd_CardDetail b on a.srcid = b.Id
 left join bd_Customers c on c.ID = b.CustomerId
 left join bd_ShopSet d on d.id = b.ShopId 
 where a.SrcID = '{$id}'";
    	$info2 = $ob->query($sql2);
    	$return['gk'] = $info2;
    	
    	//疗程项目
    	$sql3 = "select b.SaleDate, d.Shop, h.ProjectName, c.Quantity as ConsumeQuantity, g.Quantity - g.UseQuantity as LastQuantity
 from wx_SendConsumeRecord a 
 left join SS_Consume b on a.srcid = b.MDID
 left join SS_ConsumeProjectDetail c on c.MDID = b.MDID
 inner join bd_ProjectAndTimes g on g.id = c.timescardid and c.ProjectId = g.ProjectId -- 疗程项目
 left join bd_ShopSet d on d.id = b.ShopId
 left join bd_CardDetail e on e.Id = c.CardDetailId
 left join bd_CardClass f on f.Id = e.CardClassId  
 left join bd_ProjectSet h on h.ID = c.ProjectId
 where a.SrcID = '{$id}' and SUBSTRING(CardKindNo, 1, 3) = '002' ";
    	$info3 = $ob->query($sql3);
    	$return['lc'] = $info3;
    	
    	
    	//充值
    	$sql4 = "select b.SaleDate, d.Shop, h.ProjectName, c.Quantity as ConsumeQuantity, g.Quantity - g.UseQuantity as LastQuantity
    	from wx_SendConsumeRecord a
    	left join SS_Consume b on a.srcid = b.MDID
    	left join SS_ConsumeProjectDetail c on c.MDID = b.MDID
    	inner join bd_ProjectAndTimes g on g.id = c.timescardid and c.ProjectId = g.ProjectId -- 疗程项目
    	left join bd_ShopSet d on d.id = b.ShopId
    	left join bd_CardDetail e on e.Id = c.CardDetailId
    	left join bd_CardClass f on f.Id = e.CardClassId
    	left join bd_ProjectSet h on h.ID = c.ProjectId
    	where a.SrcID = '{$id}' and SUBSTRING(CardKindNo, 1, 3) = '002' ";
    	$info4 = $ob->query($sql4);
    	$return['cz'] = $info4;
    	 
    	
    	//补交
    	$sql5 = " Select b.HandDate, b.FRemarks, d.Shop,
 ISNULL(Cash, 0.00) as Cash,
 ISNULL(b.BankCard, 0.00) as Bank,
 ISNULL(UseCard, 0.00) as UseCard
 from wx_SendConsumeRecord a 
 left join HandBalance b on a.srcid = b.id
 left join bd_Customers c on c.ID = b.CustomerId
 left join bd_ShopSet d on d.id = b.ShopId  
 where a.SrcID = '{$id}'";
    	$info5 = $ob->query($sql5);
    	$return['bj'] = $info5;
    	
    	if($SrcType == '0'){
    		$return['data'] = $return['qt'];
    	}elseif($SrcType == '1'){
    		$return['data'] = $return['gk'];
    	}elseif($SrcType == '2'){
    		$return['data'] = $return['cz'];
    	}elseif($SrcType == '3'){
    		$return['data'] = $return['bj'];
    	}
    	
    	$return['note'] = array(
    				'qt'	=> '前台消费明细',
    				'gk'	=> '购卡消费明细',
    				'lc'	=> '疗程消费明细',
    				'cz'	=> '充值明细',
    				'bj'	=> '补交明细',
    			);
    	
    	
    	echo Encode($return);
    
    }
    
    
    function wx_RecommendedCustomer(){
    	
    	$fromOpenID = $_GET['fromOpenID'];
    	$openid 	= $_GET['openid'];
    	
    	if($openid == $fromOpenID){
    		return ;
    	}
    	$sql = "insert into wx_RecommendedCustomer(SrcWXOpenID, DesWXOpenID, IssueDate, State)
values('{$fromOpenID}', '{$openid}', GETDATE(), '0')";
    	
    	$sqlinfo = "select * from wx_RecommendedCustomer
    					where SrcWXOpenID='{$fromOpenID}' and
    							DesWXOpenID='{$openid}' and State = 0";
    	
    	$ob = M();
    	$info = $ob->query($sqlinfo);
    	if(empty($info)){
    		$ob->query($sqlinfo);
    	}
    	
    }
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
}